﻿using System;
using System.Collections;
using System.Reflection;
using System.Text;

using LeanEngine.Entity;
using LeanEngine.Extensions;
using LeanEngine.Collections;
using LeanEngine.Data;
using System.Threading.Tasks;

namespace LeanEngine.Data.SqlClient
{
    /// <summary>
    /// Represents an insert command to a NxN table
    /// </summary>
    internal struct SqlClientInsertRelationCommand : IDisposable
    {
        #region Method Members

        /// <summary>
        /// ExecuteNonQuery an insert command for all relation value values into the specified table.
        /// </summary>
        /// <exception cref="ArgumentNullException"></exception>
        /// <param name="table">The relation table name.</param>
        /// <param name="value">The first relation transfer object instance.</param>
        /// <param name="relationDto">The list of tranfer object values of the second relation.</param>
        public void ExecuteNonQuery(string table, PlainEntity value, IList relationDto)
        {
            if (value.IsNull()) throw new ArgumentNullException("value");
            if (table.IsNullOrEmpty()) throw new ArgumentNullException("table");
            if (relationDto.IsNullOrEmpty()) throw new ArgumentNullException("relationDto");

            if (relationDto.Count > 0)
            {
                var metaTable = MetaTableCache.GetItem(value.GetType());
                var fkMetaTable = MetaTableCache.GetItem(relationDto[0].GetType());
                var commandText = BuildSqlCommand(table, metaTable, fkMetaTable);

                foreach (var item in relationDto)
                {
                    using (var cmd = new DataCommand())
                    {
                        cmd.CommandText = commandText;

                        foreach (var property in metaTable.ColumnsList.WherePrimaryKey())
                        {
                            cmd.Parameters.Add(cmd.NewParameter(property.DBColumn.Name, property.GetValue(value)));
                        }

                        foreach (var property in fkMetaTable.ColumnsList.WherePrimaryKey())
                        {
                            cmd.Parameters.Add(cmd.NewParameter(property.DBColumn.Name, property.GetValue(item)));
                        }

                        cmd.ExecuteNonQuery();
                    }
                }
            }
        }

        private static string BuildSqlCommand(string relationTableName, MetaTable table, MetaTable fkTable)
        {
            var columns = new StringBuilder();
            var parameters = new StringBuilder();

            try
            {
                CreateParameters(table, columns, parameters);
                CreateParameters(fkTable, columns, parameters);

                var commandText = "INSERT INTO ";
                commandText += relationTableName;
                commandText += " (";
                commandText += columns.Remove(columns.Length - 2, 2).ToString();
                commandText += ") VALUES (";
                commandText += parameters.Remove(parameters.Length - 2, 2).ToString();
                commandText += ")";

                return commandText;
            }
            finally
            {
                columns.Remove(0, columns.Length);
                parameters.Remove(0, parameters.Length);

                columns = null;
                parameters = null;
            }
        }

        private static void CreateParameters(MetaTable metaTable, StringBuilder columns, StringBuilder parameters)
        {
            foreach (var property in metaTable.ColumnsList.WherePrimaryKey())
            {
                //columns.Append("[");
                columns.Append(property.DBColumn.Name);
                columns.Append(", ");

                parameters.Append("@");
                parameters.Append(property.DBColumn.Name);
                parameters.Append(", ");
            }
        }

        #endregion

        #region IDisposable Members

        /// <summary>
        /// Release all resources used
        /// </summary>
        public void Dispose()
        {
            GC.SuppressFinalize(this);
        }

        #endregion
    }
}
